package com.google.code.sharecontent;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

/**
 * инициализация базы даннях для продукта
 * 
 * @author Marat Gizatullin <maratische@gmail.com>
 * 
 */
public class DBConnect {
	static Logger logger = Logger.getLogger(DBConnect.class.getName());

	private Connection conn = null;
	private Statement s;

	public DBConnect() {
	}

	public void close() {
		// if(rs != null) rs.close();
		try {
			if (s != null)
				s.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private void createDataSource() throws NamingException, SQLException {
		final String sql = "select version()";
		InitialContext ic = new InitialContext();
		DataSource ds = (DataSource) ic.lookup("java:ShareContentMysqlDS");
		// Connection con = null;
		// Statement stmt = null;
		// ResultSet rs = null;
		try {
			conn = ds.getConnection();
			/*
			 * stmt = con.createStatement(); rs = stmt.executeQuery(sql);
			 * while(rs.next()) { System.out.println("Query '" + sql +
			 * "' returned " + rs.getString(1)); }
			 */
		} finally {
			/*
			 * if(rs != null) rs.close(); if(stmt != null) stmt.close(); if(con
			 * != null) con.close();
			 */
		}
	}

	/**
	 * иницилизируем коннект к базе данных, база данных создается в текущем
	 * каталоге, если не существует. <br/>
	 * в таблице VERSIONS храниться версия текущей базы данных, если в массиве
	 * SQL больше строк, чем версия базы данных, то проливаем необходимые
	 * строки, после этого изменяем текущую версию базы данных
	 */
	public void init(String userHome) throws ClassNotFoundException,
			SQLException {
		try {
			createDataSource();
		} catch (NamingException e1) {
			System.out.println(e1.getMessage());
		}
		/*
		 * String driver = "org.apache.derby.jdbc.EmbeddedDriver"; String
		 * connectionURL = "jdbc:derby:"+userHome+"sharecontent;create=true";
		 * 
		 * 
		 * try { Class.forName(driver); logger.info(driver + " loaded. "); }
		 * catch (java.lang.ClassNotFoundException e) { throw e; }
		 */

		try {
			// conn = DriverManager.getConnection(connectionURL);

			int versionId = -1;
			// первое и обязательно, необходимо проверить наличие таблицы
			// VERSIONS
			if (checkTable(conn, "versions", "ID = 0")) {
				String sql = "select ID from versions";
				s = conn.createStatement();
				ResultSet rs = s.executeQuery(sql);
				if (rs.next()) {
					versionId = rs.getInt("ID");
				}
			}
			s = conn.createStatement();
			logger.info("old database version id " + versionId);
			if (versionId < SQL.length - 1) {
				while (versionId < SQL.length - 1) {
					versionId++;
					logger.debug(SQL[versionId]);
					s.executeUpdate(SQL[versionId]);
					// version
					String sql = "update versions set ID=" + versionId;
					logger.debug(sql);
					s.executeUpdate(sql);
				}
			}
			logger.info("new database version id " + versionId);
		} catch (SQLException e) {
			throw e;
		}
	}

	public Connection getConnection() {
		return this.conn;
	}

	/**
	 * проверяем наличие таблицы когда возвращается код ошибки 42X05, это
	 * означает, что данной таблицы в базе данных нет
	 * 
	 * @param conn
	 * @param tableName
	 * @param testParam
	 * @return
	 * @throws SQLException
	 */
	private boolean checkTable(Connection conn, String tableName,
			String testParam) throws SQLException {
		try {
			Statement s = conn.createStatement();
			s.execute("update " + tableName + " set " + testParam
					+ " where 1=3");
		} catch (SQLException sqle) {
			String theError = (sqle).getSQLState();
			logger.info("  Utils GOT:  " + theError);
			/** If table exists will get - WARNING 02000: No row was found **/
			if (theError.equals("42X05") || theError.equals("42S02")) // Table does not exist
			{
				return false;
			} else if (theError.equals("42X14") || theError.equals("42821")) {
				logger
						.info("WwdChk4Table: Incorrect table definition. Drop table WISH_LIST and rerun this program");
				throw sqle;
			} else {
				logger.info("WwdChk4Table: Unhandled SQLException");
				throw sqle;
			}
		}
		return true;
	}

	String[] SQL = new String[] {
			"create table versions (ID INTEGER not null)",
			"insert into versions (ID) values (1)",
			//GENERATED ALWAYS AS IDENTITY
			"create table users ("
					+ "    ID INT not null auto_increment,"
					+ "    USERNAME VARCHAR (255) not null,"
					+ "    PASSWORD VARCHAR (255) not null,"
					+ "    FIRSTNAME VARCHAR (255),"
					+ "    LASTNAME VARCHAR (255),"
					+ "    STATUS_ID INT not null,"
					+ "    constraint PK_USERS primary key (ID))",
			"create unique index LOGIN_U on users(USERNAME)",
			"create table statuses("
					+ "    ID INT not null auto_increment,"
					+ "    CODE VARCHAR (100) not null,"
					+ "    NAME VARCHAR (255),"
					+ "    constraint PK_USER_STATUS primary key (ID))",
			"create table resources ("
					+ "    ID INT not null auto_increment,"
					+ "    NAME VARCHAR (255) not null,"
					+ "    URL VARCHAR (500) not null,"
					+ "    STATUS_ID INT not null,"
					+ "    USER_ID INT not null,"
					+ "    constraint PK_RESOURSES primary key (ID))",
			"create table resources_log ("
					+ "    ID INT not null auto_increment,"
					+ "    RESOURCE_ID INT not null,"
					+ "    USER_ID INT not null,"
					+ "    STATUS_ID INT not null,"
					+ "    ACTION_DATE TIMESTAMP not null,"
					+ "    constraint PK_RESOURCES_LOG primary key (ID))",
			"alter table users add constraint FK_USERS_1 foreign key (STATUS_ID) references STATUSES (ID) on delete no action on update no action",
			"alter table resources add constraint FK_RESOURSES_1 foreign key (STATUS_ID) references STATUSES (ID) on delete no action on update no action",
			"alter table resources_log add constraint FK_RESOURCES_LOG_1 foreign key (RESOURCE_ID) references RESOURCES (ID) on delete no action on update no action",
			"alter table resources_log add constraint FK_RESOURCES_LOG_2 foreign key (USER_ID) references USERS (ID) on delete no action on update no action",
			"alter table resources_log add constraint FK_RESOURCES_LOG_3 foreign key (STATUS_ID) references STATUSES (ID) on delete no action on update no action",
			"insert into statuses (CODE,NAME) values ('ADDED','livecircle.added')",
			"insert into statuses (CODE,NAME) values ('AUTH','livecircle.auth')",
			"insert into statuses (CODE,NAME) values ('WORKING','livecircle.working')",
			"insert into statuses (CODE,NAME) values ('DELETED','livecircle.deleted')",
			"insert into statuses (CODE,NAME) values ('ACTIVE','livecircle.active')",
			"insert into statuses (CODE,NAME) values ('TEMP_NOT_ACCESS','livecircle.tempNotAccess')",
			"insert into statuses (CODE,NAME) values ('BLOKED','livecircle.bloked')",
			"insert into statuses (CODE,NAME) values ('USER_ACTIVE','livecircle.userActive')",
			"insert into users (USERNAME, PASSWORD, STATUS_ID) values ('user','user',(select ID from STATUSES where CODE='USER_ACTIVE'))",
			"create table contents ("
					+ "    ID INT not null auto_increment,"
					+ "    RESOURCE_ID INT not null,"
					+ "    STATUS_ID INT not null,"
					+ "    URL VARCHAR (2500) not null,"
					+ "    TYPE_ID INT not null," 
					+ "    TITLE VARCHAR (255),"
					+ "    ARTIST VARCHAR (255)," 
					+ "    ALBUM VARCHAR (255),"
					+ "    CONTENT_YEAR VARCHAR (4),"
					+ "    GENRE VARCHAR (100)," 
					+ "    TRACK VARCHAR (100),"
					+ "    SIZE INT," 
					+ "    PARAM_DATE VARCHAR (100),"
					+ "    PARAM_LAST_MODIFIER VARCHAR (100),"
					+ "    constraint PK_CONTENTS primary key (ID))",
			"create table types ("
					+ "    ID INT not null auto_increment,"
					+ "    CODE VARCHAR (100) not null,"
					+ "    NAME VARCHAR (100),"
					+ "    constraint PK_TYPES primary key (ID))",
			"alter table contents add constraint FK_CONTENTS_1 foreign key (RESOURCE_ID) references resources (ID) on delete no action on update no action",
			"alter table contents add constraint FK_CONTENTS_2 foreign key (STATUS_ID) references statuses (ID) on delete no action on update no action",
			"alter table contents add constraint FK_CONTENTS_3 foreign key (TYPE_ID) references types (ID) on delete no action on update no action",
			"insert into types (CODE,NAME) values ('MUSIC','types.music')",
			"insert into statuses (CODE,NAME) values ('FOUNDED','livecircle.founded')",
			"alter table contents add column FILENAME  VARCHAR (2500)",//FIXME 
			"insert into statuses (CODE,NAME) values ('TO_PROCESS','livecircle.toProcess')",
			"insert into statuses (CODE,NAME) values ('WAITING_CONFIRM','livecircle.waitingСonfirm')",
			"create table playlists ("
			+ "    ID INT not null auto_increment,"
			+ "    USER_ID INT not null,"
			+ "    STATUS_ID INT not null,"
			+ "    NAME VARCHAR (255),"
			+ "    DESCRIPTION VARCHAR (255)," 
			+ "    SORT INT,"
			+ "    PIBLIC_PLAYLIST INT,"
			+ "    constraint PK_PLAYLISTS primary key (ID))",
			"create table playlist_contents ("
			+ "    ID INT not null auto_increment,"
			+ "    USER_ID INT not null,"
			+ "    CONTENT_ID INT not null,"
			+ "    PLAYLIST_ID INT not null,"
			+ "    DATE_CREATED TIMESTAMP not null, "
			+ "    ORDER_CONTENT INT," 
			+ "    constraint PK_PLAYLISTCONTENTS primary key (ID))",
			"alter table playlists add constraint FK_PLAYLISTS_1 foreign key (USER_ID) references users (ID) on delete no action on update no action",
			"alter table playlists add constraint FK_PLAYLISTS_2 foreign key (STATUS_ID) references statuses (ID) on delete no action on update no action",
			"alter table playlist_contents add constraint FK_PLAYLIST_CONTENTS_1 foreign key (USER_ID) references users (ID) on delete no action on update no action",
			"alter table playlist_contents add constraint FK_PLAYLIST_CONTENTS_2 foreign key (CONTENT_ID) references contents (ID) on delete no action on update no action",
			"alter table playlist_contents add constraint FK_PLAYLIST_CONTENTS_3 foreign key (PLAYLIST_ID) references playlists (ID) on delete no action on update no action",
			"insert into statuses (CODE,NAME) values ('NOT_ACCESS_FOR_PROCESS','livecircle.not_access_for_process')",
			"create table groups ("
			+ "    ID INT not null auto_increment,"
			+ "    CODE VARCHAR (100) not null,"
			+ "    NAME VARCHAR (100),"
			+ "    constraint PK_GROUPS primary key (ID))",
			"create table user_groups ("
			+ "    ID INT not null auto_increment,"
			+ "    USER_ID INT not null,"
			+ "    GROUP_ID INT not null,"
			+ "    constraint PK_USER_GROUPS primary key (ID))",
			"insert into groups (CODE,NAME) values ('ADMIN','user.groups.admin')",
			"insert into groups (CODE,NAME) values ('USER','user.groups.user')",
			"insert into groups (CODE,NAME) values ('ADVANCED.USER','user.groups.advanced.user')",
			
			"insert into users (USERNAME, PASSWORD, STATUS_ID) values ('admin','admin',(select ID from statuses where CODE='USER_ACTIVE'))",
			"insert into users (USERNAME, PASSWORD, STATUS_ID) values ('auser','auser',(select ID from statuses where CODE='USER_ACTIVE'))",
			"insert into user_groups (USER_ID, GROUP_ID) values ((select ID from users where USERNAME='user'),(select ID from groups where CODE='USER'))",
			"insert into user_groups (USER_ID, GROUP_ID) values ((select ID from users where USERNAME='auser'),(select ID from groups where CODE='USER'))",
			"insert into user_groups (USER_ID, GROUP_ID) values ((select ID from users where USERNAME='auser'),(select ID from groups where CODE='ADVANCED.USER'))",
			"insert into user_groups (USER_ID, GROUP_ID) values ((select ID from users where USERNAME='admin'),(select ID from groups where CODE='USER'))",
			"insert into user_groups (USER_ID, GROUP_ID) values ((select ID from users where USERNAME='admin'),(select ID from groups where CODE='ADVANCED.USER'))",
			"insert into user_groups (USER_ID, GROUP_ID) values ((select ID from users where USERNAME='admin'),(select ID from groups where CODE='ADMIN'))",
			
			"create view contents_artists_view (ARTIST, KOL) AS select ARTIST, count(*) as KOL from contents where STATUS_ID=(select ID from statuses where CODE='ACTIVE') group by ARTIST",
			"create index CONTENTS_RESOURCEID_STATUSID on CONTENTS(RESOURCE_ID, STATUS_ID)",
			//test inport
			/*"insert into RESOURCES (NAME, URL, STATUS_ID, USER_ID) values ('test1','http://test1',(select ID from STATUSES where CODE='ACTIVE'),(select ID from USERS where USERNAME='user'))",
			"insert into CONTENTS (RESOURCE_ID,STATUS_ID,URL,TYPE_ID,TITLE,ARTIST, FILENAME) values ((select ID from RESOURCES where NAME='test1'),(select ID from STATUSES where CODE='ACTIVE'),'http://test1/url1.mp3',(select ID from TYPES where CODE='MUSIC'),'TITLE1','ARTIST1', 'FILENAME1.mp3')", 
			"insert into CONTENTS (RESOURCE_ID,STATUS_ID,URL,TYPE_ID,TITLE,ARTIST, FILENAME) values ((select ID from RESOURCES where NAME='test1'),(select ID from STATUSES where CODE='ACTIVE'),'http://test1/url2.mp3',(select ID from TYPES where CODE='MUSIC'),'TITLE1','ARTIST2', 'FILENAME2.mp3')", 
			"insert into CONTENTS (RESOURCE_ID,STATUS_ID,URL,TYPE_ID,TITLE,ARTIST, FILENAME) values ((select ID from RESOURCES where NAME='test1'),(select ID from STATUSES where CODE='ACTIVE'),'http://test1/url3.mp3',(select ID from TYPES where CODE='MUSIC'),'TITLE1','ARTIST3', 'FILENAME3.mp3')", 
			"insert into CONTENTS (RESOURCE_ID,STATUS_ID,URL,TYPE_ID,TITLE,ARTIST, FILENAME) values ((select ID from RESOURCES where NAME='test1'),(select ID from STATUSES where CODE='ACTIVE'),'http://test1/url4.mp3',(select ID from TYPES where CODE='MUSIC'),'TITLE1','ARTIST4', 'FILENAME4.mp3')", 
			"insert into CONTENTS (RESOURCE_ID,STATUS_ID,URL,TYPE_ID,TITLE,ARTIST, FILENAME) values ((select ID from RESOURCES where NAME='test1'),(select ID from STATUSES where CODE='ACTIVE'),'http://test1/url5.mp3',(select ID from TYPES where CODE='MUSIC'),'TITLE1','ARTIST5', 'FILENAME5.mp3')",*/ 
	};
}
